PHP & MySQL

This is a simple introduction to databasing with MySQL and PHP. I'm not an expert, this article may use incorrect terms, and the code may not be the best or the most beautiful one, but I think it's enough so you can use MySQL for databases at your website.

I assume that you have some basic knowledge of programming, and the PHP language in particular.

Databases

A database consists of a series of tables which have data. This data can be related, but that's not a must..

I think the best way to understand what a table is, is to look at one..

The following example can be considered as a d-base table:

Table Clients
id
name
age
city
country

This is a d-base table. In this table we can insert records of data, as follows:

-------------------------------------- 
Table Clients 
id / name / age / city     / country 
-------------------------------------- 
1  / john /  22 / lisbon   / Portugal 
2  / jane /  20 / new york / U. States 
3  / rick /  19 / london   / England 
---------------------------------------

So.. you see those 'id', 'name', 'age', 'city' and 'country' fields, right? Well, these are the fields of our table. In these we insert the data. They can be of different data types (integer, text, and others..).

Now let's see how we can create a MySQL database and a table with different fields of different data types.

In the command line we type:

    mysqladmin -u root -p password create dbase

where:

-u root is the username
-p password is the password
dbase is the name of the database

Now in a text file, we type the following to create a table:

Create Table clients ( 

id tinyint(4) Default '0' Not Null Auto_Increment 
name varchar(45), 
age int(3), 
city varchar(45), 
country varchar(45), 
Primary Key (id), 
Unique id (id) 

);

Now we save the file in mysql's /bin dir as clients.dump.

And in the command line we type:

    mysql -u root -p password dbase < clients.dump

Now we have a database called dbase and a table called clients.

Before we go on, let me explain the basics of the data types and flags..

We use the "id" field to identify each record. The properties of this field are:

Default '0' - This makes the default value for this field '0'.
Not Null - This says that the camp can't be null. It must have data.
Auto_Increment - This makes the field increment the value from last record's id and use it as its value.

SQL

SQL stands for Structured Query Language. It allows us to access a database. With SQL we can retrieve data from a database, we can insert data into a database, we can delete data and we can change data that is already in the database.

We do all that with SQL Queries.

If we want to select all the records from the following table, we do a query like "Select * From Clients"

-------------------------------------- 
Table Clients 
id / name / age / city     / country 
-------------------------------------- 
1  / john /  22 / lisbon   / Portugal 
2  / jane /  20 / new york / U. States 
3  / rick /  19 / london   / England 
4  / jack /  22 / lisbon   / Portugal 
---------------------------------------

Now, imagine we just want to select the records that have Portugal as the country:

"Select * From Clients Where country = Portugal "

This returns:

1 john 22 lisbon Portugal
4 jack 22 lisbon Portugal

Note that the * character stands for ALL.

Well, we may just want to select the names of the persons that have Portugal as their country:

"Select name From Clients Where country = Portugal "

This would return:

john
jack

And we can select other fields as well:

"Select name, age From clients Where country = Portugal"

This would return:

john 22
jack 22

And define other conditions like:

"Select name, age, city, country >From clients Where age = 20"

This would return:

jane 20 new york U. States

And now, let's see how we can insert a new record in the database..

"Insert Into clients (name, age, city, country)
             Values ('jackline', '21', 'Oporto', 'Portugal')"

would insert the values Jackline 21 Oporto Portugal in the clients table.. The id value would be autoincremented..

If we need to delete records, we have the Delete Query..

"Delete From clients Where country = U.States"

would delete record number 2, Jane.

SQL is a very useful language and we can do a lot more with it.. I think I would need a book to explain all of it (and I don't know all of it).. As this is a tutorial for newbies, we'll stay with these. I'll give some more examples latter on..

PHP, SQL & MySQL

So let's put all this together..

Some useful PHP functions you'll need to know:

mysql_connect ( string host, string username, string password )

This function begins a connection to MySQL at the specified host. All the arguments in this function are optional. Sample:

           $dbase = mysql_connect("localhost", "root", "password");

mysql_select_db ( string database, integer link )

This function will select the database to be used. Sample:

           $dbase = mysql_connect ("localhost", "root", "password"); 
           mysql_select_db("database", $dbase);

mysql_query (string query, integer link )

This function executes a query to the database. You can omit the link argument, and it will use the last open connection. Sample:

           $dbase = mysql_connect ("localhost", "root", "password"); 
           mysql_select_db("database", $dbase); 
           $result = mysql_query("Select * From big_table", $dbase);

mysql_fetch_row (integer result )

This function returns an array that represents all the fields for a row. It's very similar to the mysql_fetch_array function..

           $result = mysql_query("Select * From big_table"); 
           while($row = mysql_fetch_row($result)) { 
              echo "$row[0], $row[1]<br>"; 
           }

mysql_close (integer link )

This function closes the connection to a database. If the link argument is left out, it'll close the last opened connection. The use of this function is not necessary, as the script closes all links when it finishes.

           mysql_close($dbase);

           mysql_close;

There are some more (I think there is a total of 32 mysql related functions in PHP 4) that you'll need to know in order to do more complex things.. for now, these are more than enough..

Imagine we want to be able to insert data into the table.. we'll do an Insert query to insert a new client in the table.. First let's build a little html form.. and after that we'll write the necessary PHP code to do the insert query.

<html> 

<head> 
    <title>Insert Client</title> 
</head> 

<body> 

<form action="insert.php" method=post> 

<table> 

  <tr> 
    <td>name:</td> 
    <td><input type=text name=name></td> 
  </tr> 

  <tr> 
    <td>age:</td> 
    <td><input type=text name=age></td> 
  </tr> 

  <tr> 
    <td>city:</td> 
    <td><input type=text name=city> 
  </tr> 

  <tr> 
    <td>country:</td> 
    <td><input type=text name=country></td> 
  </tr> 
  

  <tr> 
    <td><input type=hidden name=submit value="submit"></td> 
    <td><input type=submit name=insert value="insert"></td> 
  </tr> 
  

</table> 

</form> 

<? 

    $database = mysql_connect("localhost", "root"); 

    mysql_select_db("dbase", $database); 

    if($submit <> "") { 

        mysql_query("Insert Into clients (name, age, city, country) Values ('$name', '$age', '$city', '$country')", $database); 

    } 

    mysql_close; 
  

?> 
  

</body> 

</html>

This file is named insert.php.

If you fill the fields, and click the insert button, you'll insert a new record into the clients database. Now, let's see how we can get a list of all the records in that table..

<? 

    $dbase = mysql_connect("localhost", "root"); 

    mysql_select_db("dbase", $dbase); 

    $query = mysql_query("Select * From Clients"); 

    echo "<b><u><font color=red>Client List:</font></u></b>"; 
    echo "<p>"; 

    while($rows = mysql_fetch_row($query)) { 

        echo "<b>client id:</b> $rows[0]<br>"; 
        echo "<b>name: </b>$rows[1]<br>"; 
        echo "<b>age: </b>$rows[2]<br>"; 
        echo "<b>city: </b>$rows[3]<br>"; 
        echo "<b>country: </b>$rows[4]<br>"; 
 echo "<br>"; 

    } 

    mysql_close; 
  

?>

This little sample of code will get all the records in the clients table of the dbase database. We now have a complete list of our clients on our webpage..

Now let's see how we can delete a record from our database..

The filename is del.php ..

<html> 
  <head> 
    <title>Delete from Clients</title> 
  </head> 

<body> 
  

<? 

    $database =mysql_connect("localhost", "root"); 
    mysql_select_db("dbase", $database); 

    If($submit <> "") { 

 mysql_query("Delete >From clients Where id=$client"); 
  

  } 

?> 
  

<? 
  
  

   $query = mysql_query("Select * From clients"); 

    echo "<form action=\"del.php\" method=post>"; 

    echo "<select name=client>"; 

        while($rows = mysql_fetch_row($query)) { 

            echo "<option value=$rows[0]>$rows[0] $rows[1]</option>"; 

        } 

    echo "</select>"; 

    echo "<input type=hidden name=submit value=\"submit\">"; 
    echo "<input type=submit name=delete value=\"delete\">"; 

    echo "</form>"; 

    mysql_close; 

?> 

</body> 

</html>

I used a simple function to draw an html combo box with data from the database.. It shows the client id and the client name.. it's up to you to know which data identifies your to-be-deleted record best..

If you click on the delete button in this del.php file, you'll automatically delete the record you selected in the combo box..

Final Words

Well, I think these 3 small PHP scripts and the information in this article will put you coding PHP with access to MySQL databases in a couple of hours..

You should learn a little more about SQL and MySQL in order to be able to write some more complex scripts.. Good Luck..

If you need some more info or highlights, mail me at bmpc@netcabo.pt.. in the web you should check:

http://www.php.net/
http://www.mysql.org/
http://www.phpbuilder.com/
http://www.w3schools.com/sql/

bmpc

Default '0'	-	This makes the default value for this field '0'.
Not Null	-	This says that the camp can't be null. It must have data.
Auto_Increment	-	This makes the field increment the value from last record's id and use it as its value.